<?php
class ModelSaleOrder extends Model {
	public function addOrder($data) {
		M('setting/store');

		$store_info = $this->model_setting_store->getStore($data['store_id']);

		if ($store_info) {
			$data['store_name'] = $store_info['name'];
			$data['store_url'] = $store_info['url'];
		} else {
			$data['store_name'] = C('config_name');
			$data['store_url'] = HTTP_CATALOG;
		}

		M('setting/setting');

		$setting_info = $this->model_setting_setting->getSetting('setting', $data['store_id']);

		if (isset($setting_info['invoice_prefix'])) {
			$data['invoice_prefix'] = $setting_info['invoice_prefix'];
		} else {
			$data['invoice_prefix'] = C('config_invoice_prefix');
		}

		M('localisation/country');
		M('localisation/zone');

		$country_info = $this->model_localisation_country->getCountry($data['shipping_country_id']);

		if ($country_info) {
			$data['shipping_country'] = $country_info['name'];
			$data['shipping_address_format'] = $country_info['address_format'];
		} else {
			$data['shipping_country'] = '';
			$data['shipping_address_format'] = '{firstname} {lastname}' . "\n" . '{company}' . "\n" . '{address_1}' . "\n" . '{address_2}' . "\n" . '{city} {postcode}' . "\n" . '{zone}' . "\n" . '{country}';
		}

		$zone_info = $this->model_localisation_zone->getZone($data['shipping_zone_id']);

		if ($zone_info) {
			$data['shipping_zone'] = $zone_info['name'];
		} else {
			$data['shipping_zone'] = '';
		}

		$country_info = $this->model_localisation_country->getCountry($data['payment_country_id']);

		if ($country_info) {
			$data['payment_country'] = $country_info['name'];
			$data['payment_address_format'] = $country_info['address_format'];
		} else {
			$data['payment_country'] = '';
			$data['payment_address_format'] = '{firstname} {lastname}' . "\n" . '{company}' . "\n" . '{address_1}' . "\n" . '{address_2}' . "\n" . '{city} {postcode}' . "\n" . '{zone}' . "\n" . '{country}';
		}

		$zone_info = $this->model_localisation_zone->getZone($data['payment_zone_id']);

		if ($zone_info) {
			$data['payment_zone'] = $zone_info['name'];
		} else {
			$data['payment_zone'] = '';
		}

		M('localisation/currency');

		$currency_info = $this->model_localisation_currency->getCurrencyByCode(C('config_currency'));

		if ($currency_info) {
			$data['currency_id'] = $currency_info['currency_id'];
			$data['currency_code'] = $currency_info['code'];
			$data['currency_value'] = $currency_info['value'];
		} else {
			$data['currency_id'] = 0;
			$data['currency_code'] = C('config_currency');
			$data['currency_value'] = 1.00000;
		}

		$data['date_added'] = $GLOBALS['V5_NOW'];
		$data['date_modified'] = $GLOBALS['V5_NOW'];
      	$order_id = $this->db->insert('order', $data);

      	if (isset($data['order_product'])) {
      		foreach ($data['order_product'] as $order_product) {
				$order_product['order_id'] = $order_id;

				$order_product_id = $this->db->insert('order_product', $order_product);

				$this->db->query("UPDATE  @@product SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_id = " . (int)$order_product['product_id'] . " AND subtract = 1");

				if (isset($order_product['order_option'])) {
					foreach ($order_product['order_option'] as $order_option) {
						$order_option['order_id'] = $order_id;
						$order_option['order_product_id'] = $order_product_id;

						$this->db->insert('order_option', $order_option);

						$this->db->query("UPDATE  @@product_option_value SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_option_value_id = " . (int)$order_option['product_option_value_id'] . " AND subtract = 1");
					}
				}

				if (isset($order_product['order_download'])) {
					foreach ($order_product['order_download'] as $order_download) {
						$order_download['order_id'] = $order_id;
						$order_download['order_product_id'] = $order_product_id;

						$this->db->insert('order_download', $order_download);
					}
				}
			}
		}

		if (isset($data['order_voucher'])) {
			foreach ($data['order_voucher'] as $order_voucher) {
				$order_voucher['order_id'] = $order_id;

				$this->db->insert('order_voucher', $order_voucher);
      			$this->db->query("UPDATE  @@voucher SET order_id = " . (int)$order_id . " WHERE voucher_id = " . (int)$order_voucher['voucher_id']);
			}
		}

		// Get the total
		$total = 0;

		if (isset($data['order_total'])) {
      		foreach ($data['order_total'] as $order_total) {
				$order_total['order_id'] = $order_id;

				$this->db->insert('order_total', $order_total);
			}
			$total += $order_total['value'];
		}

		// Affiliate
		$affiliate_id = 0;
		$commission = 0;

		if (!empty($this->request->post['affiliate_id'])) {
			M('sale/affiliate');

			$affiliate_info = $this->model_sale_affiliate->getAffiliate($this->request->post['affiliate_id']);

			if ($affiliate_info) {
				$affiliate_id = $affiliate_info['affiliate_id'];
				$commission = ($total / 100) * $affiliate_info['commission'];
			}
		}

		// Update order total
		$this->db->query("UPDATE `@@order` SET total = " . (float)$total . ", affiliate_id = " . (int)$affiliate_id . ", commission = " . (float)$commission . " WHERE order_id = " . (int)$order_id);
	}

	public function editOrder($order_id, $data) {
		M('setting/store');
		$store_info = $this->model_setting_store->getStore($data['store_id']);

		if ($store_info) {
			$data['store_name'] = $store_info['name'];
			$data['store_url'] = $store_info['url'];
		} else {
			$data['store_name'] = C('config_name');
			$data['store_url'] = HTTP_CATALOG;
		}

		M('localisation/country');

		M('localisation/zone');

		$country_info = $this->model_localisation_country->getCountry($data['shipping_country_id']);

		if ($country_info) {
			$data['shipping_country'] = $country_info['name'];
			$data['shipping_address_format'] = $country_info['address_format'];
		} else {
			$data['shipping_country'] = '';
			$data['shipping_address_format'] = '{firstname} {lastname}' . "\n" . '{company}' . "\n" . '{address_1}' . "\n" . '{address_2}' . "\n" . '{city} {postcode}' . "\n" . '{zone}' . "\n" . '{country}';
		}

		$zone_info = $this->model_localisation_zone->getZone($data['shipping_zone_id']);

		if ($zone_info) {
			$data['shipping_zone'] = $zone_info['name'];
		} else {
			$data['shipping_zone'] = '';
		}

		$country_info = $this->model_localisation_country->getCountry($data['payment_country_id']);

		if ($country_info) {
			$data['payment_country'] = $country_info['name'];
			$data['payment_address_format'] = $country_info['address_format'];
		} else {
			$data['payment_country'] = '';
			$data['payment_address_format'] = '{firstname} {lastname}' . "\n" . '{company}' . "\n" . '{address_1}' . "\n" . '{address_2}' . "\n" . '{city} {postcode}' . "\n" . '{zone}' . "\n" . '{country}';
		}

		$zone_info = $this->model_localisation_zone->getZone($data['payment_zone_id']);

		if ($zone_info) {
			$data['payment_zone'] = $zone_info['name'];
		} else {
			$data['payment_zone'] = '';
		}

		$data['date_modified'] = $GLOBALS['V5_NOW'];

		// Restock products before subtracting the stock later on
		$order_query = $this->db->query("SELECT * FROM `@@order` WHERE order_status_id > 0 AND order_id = " . (int)$order_id);

		if ($order_query->num_rows) {
			$product_query = $this->db->query("SELECT * FROM  @@order_product WHERE order_id = " . (int)$order_id);

			foreach($product_query->rows as $product) {
				$this->db->query("UPDATE `@@product` SET quantity = (quantity + " . (int)$product['quantity'] . ") WHERE product_id = " . (int)$product['product_id'] . " AND subtract = 1");

				$option_query = $this->db->query("SELECT * FROM  @@order_option WHERE order_id = " . (int)$order_id . " AND order_product_id = " . (int)$product['order_product_id']);

				foreach ($option_query->rows as $option) {
					$this->db->query("UPDATE  @@product_option_value SET quantity = (quantity + " . (int)$product['quantity'] . ") WHERE product_option_value_id = " . (int)$option['product_option_value_id'] . " AND subtract = 1");
				}
			}
		}
		$this->db->update('order', $data, array('order_id' => $order_id));

		$this->db->query("DELETE FROM  @@order_product WHERE order_id = " . (int)$order_id);
       	$this->db->query("DELETE FROM  @@order_option WHERE order_id = " . (int)$order_id);
		$this->db->query("DELETE FROM  @@order_download WHERE order_id = " . (int)$order_id);

      	if (isset($data['order_product'])) {
      		foreach ($data['order_product'] as $order_product) {
				$order_product['order_id'] = $order_id;
				$order_product_id = $this->db->insert('order_product', $order_product);

				$this->db->query("UPDATE  @@product SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_id = " . (int)$order_product['product_id'] . " AND subtract = 1");

				if (isset($order_product['order_option'])) {
					foreach ($order_product['order_option'] as $order_option) {
						$order_option['order_id'] = $order_id;
						$order_option['order_product_id'] = $order_product_id;
						$this->db->insert('order_option', $order_option);

						$this->db->query("UPDATE  @@product_option_value SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_option_value_id = " . (int)$order_option['product_option_value_id'] . " AND subtract = 1");
					}
				}

				if (isset($order_product['order_download'])) {
					foreach ($order_product['order_download'] as $order_download) {
						$order_download['order_id'] = $order_id;
						$order_download['order_product_id'] = $order_product_id;
						$this->db->insert('order_download', $order_download);
					}
				}
			}
		}

		$this->db->query("DELETE FROM  @@order_voucher WHERE order_id = " . (int)$order_id);

		if (isset($data['order_voucher'])) {
			foreach ($data['order_voucher'] as $order_voucher) {
				$order_voucher['order_id'] = $order_id;
				$this->db->insert('order_voucher', $order_voucher);

				$this->db->query("UPDATE  @@voucher SET order_id = " . (int)$order_id . " WHERE voucher_id = " . (int)$order_voucher['voucher_id']);
			}
		}

		// Get the total
		$total = 0;

		$this->db->query("DELETE FROM  @@order_total WHERE order_id = " . (int)$order_id);

		if (isset($data['order_total'])) {
      		foreach ($data['order_total'] as $order_total) {
				$order_total['order_id'] = $order_id;
				$this->db->insert('order_total', $order_total);
			}
			$total += $order_total['value'];
		}

		// Affiliate
		$affiliate_id = 0;
		$commission = 0;

		if (!empty($this->request->post['affiliate_id'])) {
			M('sale/affiliate');

			$affiliate_info = $this->model_sale_affiliate->getAffiliate($this->request->post['affiliate_id']);

			if ($affiliate_info) {
				$affiliate_id = $affiliate_info['affiliate_id'];
				$commission = ($total / 100) * $affiliate_info['commission'];
			}
		}

		$this->db->query("UPDATE `@@order` SET total = " . (float)$total . ", affiliate_id = " . (int)$affiliate_id . ", commission = " . (float)$commission . " WHERE order_id = " . (int)$order_id);
	}

	public function deleteOrder($order_id) {
		$total = $this->db->queryOne("SELECT COUNT(*) as total FROM `@@order` WHERE order_status_id > 0 AND order_id = " . (int)$order_id);

		if ($total) {
			$product_query = $this->db->query("SELECT * FROM  @@order_product WHERE order_id = " . (int)$order_id);

			foreach($product_query->rows as $product) {
				$this->db->query("UPDATE `@@product` SET quantity = (quantity + " . (int)$product['quantity'] . ") WHERE product_id = " . (int)$product['product_id'] . " AND subtract = 1");

				$option_query = $this->db->query("SELECT * FROM  @@order_option WHERE order_id = " . (int)$order_id . " AND order_product_id = " . (int)$product['order_product_id']);

				foreach ($option_query->rows as $option) {
					$this->db->query("UPDATE  @@product_option_value SET quantity = (quantity + " . (int)$product['quantity'] . ") WHERE product_option_value_id = " . (int)$option['product_option_value_id'] . " AND subtract = 1");
				}
			}
		}

		$this->db->query("DELETE FROM `@@order` WHERE order_id = " . (int)$order_id);
		$this->db->query("DELETE FROM  @@order_product WHERE order_id = " . (int)$order_id);
      	$this->db->query("DELETE FROM  @@order_option WHERE order_id = " . (int)$order_id);
		$this->db->query("DELETE FROM  @@order_download WHERE order_id = " . (int)$order_id);
		$this->db->query("DELETE FROM  @@order_service WHERE order_id = " . (int)$order_id);
		$this->db->query("DELETE FROM  @@order_voucher WHERE order_id = " . (int)$order_id);
      	$this->db->query("DELETE FROM  @@order_total WHERE order_id = " . (int)$order_id);
		$this->db->query("DELETE FROM  @@order_history WHERE order_id = " . (int)$order_id);
		$this->db->query("DELETE FROM  @@order_shipping_history WHERE order_id = " . (int)$order_id);
		$this->db->query("DELETE FROM  @@order_fraud WHERE order_id = " . (int)$order_id);
		$this->db->query("DELETE FROM  @@customer_transaction WHERE order_id = " . (int)$order_id);
		$this->db->query("DELETE FROM  @@customer_reward WHERE order_id = " . (int)$order_id);
		$this->db->query("DELETE FROM  @@affiliate_transaction WHERE order_id = " . (int)$order_id);
	}

	/**
	 * 
	 * @param $order_id
	 * @return array
	 */
	public function getOrder($order_id) {
		$order = $this->db->queryOne("SELECT *, (SELECT CONCAT(c.firstname, ' ', c.lastname) FROM  @@customer c WHERE c.customer_id = o.customer_id) AS customer
				FROM `@@order` o WHERE o.order_id = $order_id");

		if ($order) {

			$order['reward'] = $this->db->queryOne("SELECT SUM(reward) as amount FROM  @@order_product WHERE order_id = $order_id");
			$order['payment_zone_code'] = $this->db->queryOne("SELECT code FROM `@@zone` WHERE zone_id = " . (int)$order['payment_zone_id']);
			$order['shipping_zone_code'] = $this->db->queryOne("SELECT code FROM `@@zone` WHERE zone_id = " . (int)$order['shipping_zone_id']);

			//affiliate
			$order['affiliate_firstname'] = '';
			$order['affiliate_lastname'] = '';			
			
			if ($order['affiliate_id']) {
				M('sale/affiliate');
				$affiliate_info = $this->model_sale_affiliate->getAffiliate($order['affiliate_id']);
				if (!empty($affiliate_info)) {
					$order['affiliate_firstname'] = $affiliate_info['firstname'];
					$order['affiliate_lastname'] = $affiliate_info['lastname'];
				}
			}

			//language
			$order['language_code'] = '';
			$order['language_filename'] = '';
			$order['language_directory'] = '';

			M('localisation/language');
			$language_info = $this->model_localisation_language->getLanguage($order['language_id']);
			if ($language_info) {
				$order['language_code'] = $language_info['code'];
				$order['language_filename'] = $language_info['filename'];
				$order['language_directory'] = $language_info['directory'];
			}

			return $order;
		} else {
			return false;
		}
	}

	public function getOrders($filter) {
		$sql = "SELECT o.*, CONCAT(o.firstname, ' ', o.lastname) AS customer FROM `@@order` o";

		if ($filter->get('filter_model')) {
			$sql .= " LEFT JOIN @@order_product op ON o.order_id=op.order_id";
		}

		if ($filter->get('filter_order_status_id') !== null) {
			$sql .= " WHERE o.order_status_id = " . (int)$filter->get('filter_order_status_id');
		} else {
			$sql .= " WHERE o.order_status_id > 0";
		}

		if ($filter->get('filter_order_id')) {
			$sql .= " AND o.order_id = " . (int)$filter->get('filter_order_id');
		}

		if ($filter->get('filter_customer')) {
			$sql .= " AND (CONCAT(o.firstname, ' ', o.lastname) LIKE '%" . ES($filter->get('filter_customer')) . "%'";
			$sql .= "OR email LIKE '%" . ES($filter->get('filter_customer')) . "%')";
		}

		if ($filter->get('filter_date_start')) {
			$sql .= " AND DATE(o.date_added) >= '" . ES($filter->get('filter_date_start')) . "'";
		}

		if ($filter->get('filter_date_end')) {
			$sql .= " AND DATE(o.date_added) <= '" . ES($filter->get('filter_date_end')) . "'";
		}

		if ($filter->get('filter_total')) {
			$sql .= " AND o.total > " . (float)$filter->get('filter_total');
		}

		if ($filter->get('filter_model')) {
			$sql .= " AND op.model='" . ES($filter->get('filter_model')) . "'";
		}

		if ($filter->get('order_basket_id') !== null) {
			$sql .= " AND o.order_basket_id = " . $filter->get('order_basket_id');
		}
		
		if ($filter->get('customer_id') !== null) {
			$sql .= " AND o.customer_id = " . $filter->get('customer_id');
		}
		
		$sql .= $filter->sort('o.order_id', 'DESC');
		$sql .= $filter->limit();
		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function getOrderProducts($order_id) {
		$query = $this->db->query("SELECT op.*, p.image FROM  @@order_product op LEFT JOIN @@product p ON (op.product_id=p.product_id) WHERE order_id = " . (int)$order_id);

		return $query->rows;
	}

	public function getOrderShippingHistory($order_id) {
		$query = $this->db->query("SELECT * FROM  @@order_shipping_history WHERE order_id = " . (int)$order_id);
		foreach ($query->rows as $i => $row) {
			$query->rows[$i]['products'] = unserialize($row['products']);
		}
		return $query->rows;
	}

	public function addShippingHistory($order_id, $data) {
		foreach ($data['products'] as $product_id => $qty) {
			$this->db->runSql("UPDATE @@order_product SET shipped_qty=shipped_qty+$qty WHERE order_id = $order_id AND product_id = $product_id");
		}
		$data['order_id'] = $order_id;
		$data['products'] = serialize($data['products']);
		$this->db->insert('order_shipping_history', $data);
	}

	public function addService($order_id, $data) {
		$data['order_id'] = $order_id;
		$data['date_added'] = $GLOBALS['V5_NOW'];
		$data['user'] = $this->user->getUserName();
		$this->db->insert("order_service", $data);
	}
	
	public function getServices($order_id, $start = 0, $limit = 10) {
		$query = $this->db->query("SELECT * FROM  @@order_service WHERE order_id = $order_id ORDER BY date_added DESC LIMIT $start, $limit");
		return $query->rows;
	}
	
	public function getTotalServices($order_id) {
		return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@order_service WHERE order_id = $order_id");
	}
	
	public function getOrderOption($order_id, $order_option_id) {
		$query = $this->db->query("SELECT * FROM  @@order_option WHERE order_id = " . (int)$order_id . " AND order_option_id = " . (int)$order_option_id);

		return $query->row;
	}

	public function getOrderOptions($order_id, $order_product_id) {
		$query = $this->db->query("SELECT * FROM  @@order_option WHERE order_id = " . (int)$order_id . " AND order_product_id = " . (int)$order_product_id);

		return $query->rows;
	}

	public function getOrderDownloads($order_id, $order_product_id) {
		$query = $this->db->query("SELECT * FROM  @@order_download WHERE order_id = " . (int)$order_id . " AND order_product_id = " . (int)$order_product_id);

		return $query->rows;
	}

	public function getOrderVouchers($order_id) {
		$query = $this->db->query("SELECT * FROM  @@order_voucher WHERE order_id = " . (int)$order_id);

		return $query->rows;
	}

	public function getOrderVoucherByVoucherId($voucher_id) {
      	return $this->db->queryOne("SELECT * FROM `@@order_voucher` WHERE voucher_id = " . (int)$voucher_id);
	}

	public function getOrderTotals($order_id) {
		$query = $this->db->query("SELECT * FROM  @@order_total WHERE order_id = " . (int)$order_id . " ORDER BY sort_order");

		return $query->rows;
	}

	public function getTotalOrders($filter = null) {
      	$sql = "SELECT COUNT(*) AS total FROM `@@order` o";

      	if ($filter) {
			if ($filter->get('filter_model')) {
				$sql .= " LEFT JOIN @@order_product op ON o.order_id=op.order_id";
			}
	
			if ($filter->get('filter_order_status_id') !== null) {
				$sql .= " WHERE o.order_status_id = " . (int)$filter->get('filter_order_status_id');
			} else {
				$sql .= " WHERE o.order_status_id > 0";
			}
	
			if ($filter->get('filter_order_id')) {
				$sql .= " AND o.order_id = " . (int)$filter->get('filter_order_id');
			}
	
			if ($filter->get('filter_customer')) {
				$sql .= " AND (CONCAT(o.firstname, ' ', o.lastname) LIKE '%" . ES($filter->get('filter_customer')) . "%'";
				$sql .= "OR email LIKE '%" . ES($filter->get('filter_customer')) . "%')";
			}
	
			if ($filter->get('filter_date_start')) {
				$sql .= " AND DATE(o.date_added) >= '" . ES($filter->get('filter_date_start')) . "'";
			}
	
			if ($filter->get('filter_date_end')) {
				$sql .= " AND DATE(o.date_added) <= '" . ES($filter->get('filter_date_end')) . "'";
			}
	
			if ($filter->get('filter_total')) {
				$sql .= " AND o.total > " . (float)$filter->get('filter_total');
			}
	
			if ($filter->get('filter_model')) {
				$sql .= " AND op.model='" . ES($filter->get('filter_model')) . "'";
			}
	
			if ($filter->get('order_basket_id') !== null) {
				$sql .= " AND o.order_basket_id = " . $filter->get('order_basket_id');
			}
			
			if ($filter->get('customer_id') !== null) {
				$sql .= " AND o.customer_id = " . $filter->get('customer_id');
			}
      	}
		return $this->db->queryOne($sql);
	}

	public function getTotalOrdersByStoreId($store_id) {
      	return $this->db->queryOne("SELECT COUNT(*) AS total FROM `@@order` WHERE store_id = " . (int)$store_id);
	}

	public function getTotalOrdersByOrderStatusId($order_status_id) {
      	return $this->db->queryOne("SELECT COUNT(*) AS total FROM `@@order` WHERE order_status_id = " . (int)$order_status_id . " AND order_status_id > 0");
	}

	public function getTotalOrdersByLanguageId($language_id) {
      	return $this->db->queryOne("SELECT COUNT(*) AS total FROM `@@order` WHERE language_id = " . (int)$language_id . " AND order_status_id > 0");
	}

	public function getTotalOrdersByCurrencyId($currency_id) {
      	return $this->db->queryOne("SELECT COUNT(*) AS total FROM `@@order` WHERE currency_id = " . (int)$currency_id . " AND order_status_id > 0");
	}

	public function getTotalSales() {
      	return $this->db->queryOne("SELECT SUM(total) AS total FROM `@@order` WHERE order_status_id > 0");
	}

	public function getTotalSalesByYear($year) {
      	return $this->db->queryOne("SELECT SUM(total) AS total FROM `@@order` WHERE order_status_id > 0 AND YEAR(date_added) = '" . (int)$year . "'");
	}

	public function createInvoiceNo($order_id) {
		$order_info = $this->getOrder($order_id);

		if ($order_info && !$order_info['invoice_no']) {
			$query = $this->db->query("SELECT MAX(invoice_no) AS invoice_no FROM `@@order` WHERE invoice_prefix = '" . ES($order_info['invoice_prefix']) . "'");

			if ($query->row['invoice_no']) {
				$invoice_no = $query->row['invoice_no'] + 1;
			} else {
				$invoice_no = 1;
			}

			$this->db->query("UPDATE `@@order` SET invoice_no = '" . (int)$invoice_no . "', invoice_prefix = '" . ES($order_info['invoice_prefix']) . "' WHERE order_id = " . (int)$order_id);

			return $order_info['invoice_prefix'] . $invoice_no;
		}
	}

	public function addOrderHistory($order_id, $data) {
		$this->db->query("UPDATE `@@order` SET order_status_id = " . (int)$data['order_status_id'] . ", date_modified = '{$GLOBALS['V5_NOW']}' WHERE order_id = " . (int)$order_id);

		$this->db->query("INSERT INTO  @@order_history SET order_id = " . (int)$order_id . ", order_status_id = " . (int)$data['order_status_id'] . ", notify = " . (isset($data['notify']) ? (int)$data['notify'] : 0) . ", comment = '" . ES(strip_tags($data['comment'])) . "', date_added = '{$GLOBALS['V5_NOW']}'");

		$order_info = $this->getOrder($order_id);

		if (C('config_complete_status_id') == $data['order_status_id']) {
			//update statistics data
			if ($data['order_status_id'] != $order_info['order_status_id']) {
				$this->db->query("UPDATE @@customer SET orders=orders+1, amount=amount+{$order_info['total']} WHERE customer_id={$order_info['customer_id']}");
				
				$products = $this->db->queryArray("SELECT product_id,quantity FROM @@order_product WHERE order_id=$order_id", 'product_id', 'quantity');
				
				foreach ($products as $product_id => $quantity) {
					$this->db->query("UPDATE @@product SET sold=sold+$quantity WHERE product_id = $product_id");
				}
			}
			
			// Send out any gift voucher mails
			M('sale/voucher');

			$results = $this->getOrderVouchers($order_id);

			foreach ($results as $result) {
				$this->model_sale_voucher->sendVoucher($result['voucher_id']);
			}
			
			
		}

      	if ($data['notify']) {
      		$params = array(
      			'{order_id}' => $order_id,
      			'{order_firstname}' => $order_info['firstname'],
      			'{order_lastname}' => $order_info['lastname'],
      			'{order_date_added}' => v5_date_format($order_info['date_added']),
      			'{order_status}' => '',
      			'{order_comment}' => strip_tags(html_entity_decode($data['comment'], ENT_QUOTES, 'UTF-8'))
      		);

			$order_status = $this->db->queryOne("SELECT name FROM  @@order_status WHERE order_status_id = '" . (int)$data['order_status_id'] . "' AND language_id = '" . (int)$order_info['language_id'] . "'");

			if ($order_status) {
				$params['{order_status}'] = $order_status;
			}
			
			send_mail($order_info['email'], 'order_status_update', $params);
		}
	}

	public function getOrderHistories($order_id, $start = 0, $limit = 10) {
		if ($start < 0) $start = 0;
		if ($limit < 1) $limit = 10;

		$query = $this->db->query("SELECT oh.date_added, os.name AS status, oh.comment, oh.notify FROM  @@order_history oh LEFT JOIN  @@order_status os ON oh.order_status_id = os.order_status_id WHERE oh.order_id = " . (int)$order_id . " AND os.language_id = " . (int)C('config_language_id') . " ORDER BY oh.date_added ASC LIMIT " . (int)$start . "," . (int)$limit);

		return $query->rows;
	}

	public function getTotalOrderHistories($order_id) {
	  	return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@order_history WHERE order_id = " . (int)$order_id);
	}

	public function getTotalOrderHistoriesByOrderStatusId($order_status_id) {
	  	return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@order_history WHERE order_status_id = " . (int)$order_status_id);
	}

	public function getEmailsByProductsOrdered($products, $start, $end) {
		$implode = array();

		foreach ($products as $product_id) {
			$implode[] = "op.product_id = '" . $product_id . "'";
		}

		$query = $this->db->query("SELECT DISTINCT email FROM `@@order` o LEFT JOIN  @@order_product op ON (o.order_id = op.order_id) WHERE (" . implode(" OR ", $implode) . ") AND o.order_status_id <> 0");

		return $query->rows;
	}

	public function getTotalEmailsByProductsOrdered($products) {
		$implode = array();

		foreach ($products as $product_id) {
			$implode[] = "op.product_id = '" . $product_id . "'";
		}

		return $this->db->queryOne("SELECT COUNT(DISTINCT email) AS total FROM `@@order` o LEFT JOIN  @@order_product op ON (o.order_id = op.order_id) WHERE (" . implode(" OR ", $implode) . ") AND o.order_status_id <> 0");
	}

	public function getOrdersByBasketId($order_basket_id) {
		return $this->db->queryArray("SELECT order_id FROM @@order WHERE order_basket_id=$order_basket_id");
	}
}
?>